package cn.sw.system.onemany.agg.zhujie;

import cn.sw.system.onemany.mapper.GenericMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.lang.reflect.*;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class DynamicQueryService {

    @Autowired
    private GenericMapper genericMapper;

    /**
     * 嵌套查询服务
     */
    public <T> List<T> queryByVO(Class<T> voClass) throws Exception {
        String mainTable = getTableName(voClass);
        List<Field> oneManyFields = getOneManyFields(voClass);

        // 先获取主表字段
        List<String> allColumns = new ArrayList<>(getColumnsFromFields(voClass, mainTable));
        String mainAlias = getTableAlias(mainTable);

        // 构建基础 SQL（只有主表）
        String sql = buildSelectSQL(mainTable, allColumns);

        // 如果有子表，构建 JOIN 查询并添加子表字段
        if (!oneManyFields.isEmpty()) {
            for (Field field : oneManyFields) {
                OneMany oneMany = field.getAnnotation(OneMany.class);
                Class<?> subClass = oneMany.targetEntity();
                String mappedBy = oneMany.mappedBy();
                String foreignKey = oneMany.foreignKey();

                String subTable = getTableName(subClass);
                String subAlias = getTableAlias(subTable);

                // 获取子表字段并加入 SELECT 列表
                List<String> subColumns = getColumnsFromSubFields(subClass, subAlias);
                allColumns.addAll(subColumns);

                // 重新构建完整的 SELECT 语句（包含主表和子表字段）
                sql = buildSelectSQL(mainTable, allColumns);

                // 拼接 JOIN 子句
                sql += String.format(" LEFT JOIN %s %s ON %s.%s = %s.%s",
                        subTable,
                        subAlias,
                        mainAlias, mappedBy,     // 主表字段（如 id）
                        subAlias, foreignKey);  // 子表字段（如 sys_article_id）
            }
        }

        List<Map<String, Object>> resultMaps = genericMapper.executeCustomSQL(sql);
        return mapToNestedVO(resultMaps, voClass, oneManyFields);
    }


    private String getTableName(Class<?> voClass) {
        if (voClass.isAnnotationPresent(Relation.class)) {
            Relation annotation = voClass.getAnnotation(Relation.class);
            if (!annotation.TableName().isEmpty()) {
                return annotation.TableName();
            }
        }
        return "sys_" + voClass.getSimpleName().toLowerCase(); // 默认规则
    }

    private List<String> getColumnsFromFields(Class<?> voClass, String tableName) {
        List<String> columns = new ArrayList<>();
        String tableAlias = getTableAlias(tableName);

        for (Field field : voClass.getDeclaredFields()) {
            if (field.isAnnotationPresent(OneMany.class)) continue;

            if (field.isAnnotationPresent(Relation.class)) {
                Relation relation = field.getAnnotation(Relation.class);
                String columnName = relation.FieldName();
                columns.add(tableAlias + "." + columnName + " AS " + field.getName());
            }
        }
        return columns;
    }

    private List<String> getColumnsFromSubFields(Class<?> subClass, String tableAlias) {
        List<String> columns = new ArrayList<>();

        for (Field field : subClass.getDeclaredFields()) {
            if (field.isAnnotationPresent(Relation.class)) {
                Relation relation = field.getAnnotation(Relation.class);
                String columnName = relation.FieldName();
                columns.add(tableAlias + "." + columnName + " AS " + field.getName());
            }
        }
        return columns;
    }

    private String buildSelectSQL(String tableName, List<String> columns) {
        String tableAlias = getTableAlias(tableName);
        return "SELECT " + String.join(", ", columns) +
                " FROM " + tableName + " " + tableAlias;
    }

    private List<Field> getOneManyFields(Class<?> voClass) {
        return Arrays.stream(voClass.getDeclaredFields())
                .filter(field -> field.isAnnotationPresent(OneMany.class))
                .collect(Collectors.toList());
    }

    private <T> List<T> mapToNestedVO(List<Map<String, Object>> resultMaps,
                                      Class<T> voClass,
                                      List<Field> oneManyFields) throws Exception {
        Map<Object, T> mainMap = new HashMap<>();
        Map<Field, Map<Object, List<Object>>> subMap = new HashMap<>();

        for (Field field : oneManyFields) {
            subMap.put(field, new HashMap<>());
        }

        for (Map<String, Object> row : resultMaps) {
            T mainVO = MapToBeanConverter.convert(row, voClass);
            Field idField = getIdField(voClass);
            Object id = getFieldValue(mainVO, idField);

            if (!mainMap.containsKey(id)) {
                mainMap.put(id, mainVO);
            }

            for (Field field : oneManyFields) {
                OneMany oneMany = field.getAnnotation(OneMany.class);
                Class<?> subClass = oneMany.targetEntity();

                Object subVO = MapToBeanConverter.convert(row, subClass);
                if (subVO != null) {
                    subMap.get(field).computeIfAbsent(id, k -> new ArrayList<>()).add(subVO);
                }
            }
        }

        for (Object id : mainMap.keySet()) {
            T mainVO = mainMap.get(id);
            for (Field field : oneManyFields) {
                List<?> subList = subMap.get(field).getOrDefault(id, Collections.EMPTY_LIST);

                Method setter = findSetter(mainVO.getClass(), field.getName(), List.class);
                if (setter != null) {
                    try {
                        setter.invoke(mainVO, subList);
                    } catch (Exception e) {
                        // 忽略异常或记录日志
                    }
                } else {
                    field.setAccessible(true);
                    field.set(mainVO, subList);
                }
            }
        }

        return new ArrayList<>(mainMap.values());
    }

    private Field getIdField(Class<?> voClass) throws NoSuchFieldException {
        if (voClass.isAnnotationPresent(Relation.class)) {
            Relation relation = voClass.getAnnotation(Relation.class);
            String primaryKey = relation.PrimaryKey();

            for (Field field : voClass.getDeclaredFields()) {
                if (field.getName().equals(primaryKey)) {
                    return field;
                }

                if (field.isAnnotationPresent(Relation.class)) {
                    Relation fieldRelation = field.getAnnotation(Relation.class);
                    if (fieldRelation.FieldName().equals(primaryKey)) {
                        return field;
                    }
                }
            }
        }
        return voClass.getDeclaredField("id");
    }

    private Object getFieldValue(Object obj, Field field) throws Exception {
        Method getter = findGetter(obj.getClass(), field.getName());
        if (getter != null) {
            return getter.invoke(obj);
        } else {
            field.setAccessible(true);
            return field.get(obj);
        }
    }

    private Method findGetter(Class<?> clazz, String fieldName) {
        String getterName = "get" + Character.toUpperCase(fieldName.charAt(0)) + fieldName.substring(1);
        try {
            return clazz.getMethod(getterName);
        } catch (NoSuchMethodException ignored) {
            return null;
        }
    }

    private Method findSetter(Class<?> clazz, String fieldName, Class<?> paramType) {
        String setterName = "set" + Character.toUpperCase(fieldName.charAt(0)) + fieldName.substring(1);
        try {
            return clazz.getMethod(setterName, paramType);
        } catch (NoSuchMethodException ignored) {
            return null;
        }
    }

    private String getTableAlias(String tableName) {
        String[] parts = tableName.split("_");
        StringBuilder alias = new StringBuilder();
        for (String part : parts) {
            if (!part.isEmpty()) {
                alias.append(part.charAt(0));
            }
        }
        return alias.toString(); // 如 sys_articles -> sa
    }
}
